using DBUtil.MetaData;
using DBUtil.Provider.SqlServer.MetaData;
using DBUtil.Util;
using DotNetCommon.Data;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using Index = DBUtil.MetaData.Index;

namespace DBUtil.Provider.SqlServer
{
    /// <summary>
    /// SqlServer数据库管理对象
    /// </summary>
    public class SqlServerManage : DBManage
    {
        /// <summary>
        /// 根据DBAccess创建DBManage
        /// </summary>
        /// <param name="db"></param>
        public SqlServerManage(DBAccess db) : base(db) { }

        #region 获取元数据信息
        /// <summary>
        /// 获取表元数据信息
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <param name="isDetail">是否是填充详细信息</param>
        /// <returns></returns>
        public override List<Table> ShowTables(string schemaName, bool isDetail)
        {
            string sql = $@"
select t.*,o.create_date,o.modify_date, e.value from INFORMATION_SCHEMA.TABLES t
left join sys.objects o on OBJECT_ID(t.TABLE_NAME)=o.object_id 
left join sys.extended_properties e on OBJECT_ID(t.TABLE_NAME)=e.major_id and e.minor_id=0 and e.name='MS_Description'
 where t.TABLE_TYPE='BASE TABLE' and t.TABLE_SCHEMA='{db.DealSqlInject(schemaName)}'";
            string sql_size = @"
if object_id(N'tempdb..#tmp_tbl_size',N'U') is not null
    DROP table #tmp_tbl_size;
create table #tmp_tbl_size(
	name varchar(255),
	rows bigint,
	reserved varchar(20),
	data varchar(50),
	index_size varchar(50),
	unused varchar(20)
)
truncate table #tmp_tbl_size
exec sp_MSforeachtable 'insert into #tmp_tbl_size exec sp_spaceused ''?'''
select * from #tmp_tbl_size";
            using (db.OpenLongConnectOnce())
            {
                var dt = db.SelectDataTable(sql);
                var dt2 = db.SelectDataTable(sql_size);
                var tables = new List<Table>();
                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    var tbl = new SqlServerTable()
                    {
                        SchemaName = dt.Rows[i]["TABLE_SCHEMA"].ToString(),
                        Name = dt.Rows[i]["TABLE_NAME"].ToString(),
                        Desc = dt.Rows[i]["value"].ToString()
                    };
                    var createTime = dt.Rows[i]["create_date"].ToString();
                    if (!string.IsNullOrWhiteSpace(createTime))
                    {
                        tbl.CreateTime = DateTime.Parse(createTime);
                    }
                    var updatetime = dt.Rows[i]["modify_date"].ToString();
                    if (!string.IsNullOrWhiteSpace(updatetime))
                    {
                        tbl.LastUpdate = DateTime.Parse(updatetime);
                    }
                    tables.Add(tbl);
                    //有些表不能通过sp_MSforeachtable访问到,比如:cdc.captured_columns等系列表(sql变更数据捕获)
                    DataRow datarow = null;
                    for (int j = 0; j < dt2.Rows.Count; j++)
                    {
                        var tblname = dt2.Rows[j]["name"].ToString();
                        if (tblname == tbl.Name || tblname == $"[{tbl.SchemaName}].[{tbl.Name}]")
                        {
                            datarow = dt2.Rows[j];
                            break;
                        }
                    }
                    if (datarow == null)
                    {
                        //处理sp_MSforeachtable不能访问到的表
                        var _tmpdt = db.SelectDataTable($"exec sp_spaceused '[{tbl.SchemaName}].[{tbl.Name}]'");
                        if (_tmpdt.Rows.Count > 0)
                        {
                            datarow = _tmpdt.Rows[0];
                        }
                    }
                    if (datarow != null)
                    {
                        tbl.RowCount = int.Parse(datarow["rows"].ToString());
                        tbl.ReservedSize = datarow["reserved"].ToString();
                        try
                        {
                            if (tbl.ReservedSize.EndsWith(" KB"))
                            {
                                var size = tbl.ReservedSize.Replace(" KB", "").To<int>() * 1024;
                                if (size > 1024) tbl.ReservedSize = DotNetCommon.UnitConverter.Humanize(size);
                            }
                        }
                        catch { }
                        tbl.DataSize = datarow["data"].ToString();
                        tbl.IndexSize = datarow["index_size"].ToString();
                        tbl.UnUsedSize = datarow["unused"].ToString();
                        #region sql变更数据捕获表自动赋予说明
                        if (tbl.Desc.IsNullOrEmptyOrWhiteSpace())
                        {
                            switch (tbl.SchemaName + "." + tbl.Name)
                            {
                                case "cdc.captured_columns":
                                case "cdc.change_tables":
                                case "cdc.ddl_history":
                                case "cdc.index_columns":
                                case "cdc.lsn_time_mapping":
                                case "dbo.systranschemas":
                                    {
                                        tbl.Desc = "[自动说明: 变更数据捕获用表]";
                                        break;
                                    }
                            }
                            if (tbl.Desc.IsNullOrEmptyOrWhiteSpace())
                            {
                                if (tbl.SchemaName == "cdc" && tbl.Name.EndsWith("_CT"))
                                {
                                    var instname = tbl.Name.Substring(0, tbl.Name.Length - 3);
                                    var count = db.SelectScalar<int>($"select count(1) from cdc.change_tables where capture_instance='{instname}'");
                                    if (count == 1)
                                    {
                                        if (instname.IndexOf('_') > 0)
                                        {
                                            var index = instname.IndexOf('_');
                                            instname = instname.Substring(0, index) + "." + instname.Substring(index + 1);
                                        }
                                        tbl.Desc = $"[自动说明: 变更数据捕获实例表({instname})]";
                                    }
                                }
                            }
                        }
                        #endregion


                    }
                    if (isDetail)
                    {
                        FillTableDetail(tbl, false);
                    }
                }
                return tables;
            };
        }
        /// <summary>
        /// 返回指定表的详细元数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName">模式名称</param>
        public override Table ShowTableDetail(string tableName, string schemaName = null)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return null;
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            return FillTableDetail(new SqlServerTable() { Name = tableName, SchemaName = schemaName }, true);
        }

        /// <summary>
        /// 返回指定视图的详细元数据
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="schemaName">模式名称</param>
        public override View ShowViewDetail(string viewName, string schemaName = null)
        {
            if (string.IsNullOrWhiteSpace(viewName)) return null;
            (viewName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(viewName, schemaName);
            var view = _showViews(viewName, schemaName).FirstOrDefault();
            if (view == null) return view;
            string sql = $@"
select 
	序号=ROW_NUMBER() OVER(order BY ORDINAL_POSITION),
	列名=T.COLUMN_NAME,
	类型=T.DATA_TYPE,
    T.CHARACTER_MAXIMUM_LENGTH,
	T.NUMERIC_PRECISION,
	T.NUMERIC_PRECISION_RADIX,
	T.NUMERIC_SCALE,
	T.DATETIME_PRECISION,
    是否可空=T.IS_NULLABLE,
	说明=tt.说明  
from INFORMATION_SCHEMA.COLUMNS T
--关联列说明
left outer join
(select 列名=c.name,说明=p.value
from sys.columns c 
	left outer join sys.extended_properties p on c.column_id = p.minor_id and c.object_id=p.major_id
where c.object_id=OBJECT_ID('{schemaName}.{viewName}'))  tt
on t.COLUMN_NAME=tt.列名
where t.TABLE_NAME='{viewName}' and t.TABLE_SCHEMA='{schemaName}'";
            DataTable dt = db.SelectDataTable(sql);
            List<Column> columns = new List<Column>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    var column = new SqlServerColumn();
                    column.Name = dt.Rows[i]["列名"].ToString();
                    column.TableName = viewName;
                    //列类型
                    column.DATA_TYPE = dt.Rows[i]["类型"].ToString();
                    column.CHARACTER_MAXIMUM_LENGTH = dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    column.NUMERIC_PRECISION = dt.Rows[i]["NUMERIC_PRECISION"].ToString();
                    column.DATETIME_PRECISION = dt.Rows[i]["DATETIME_PRECISION"].ToString();
                    column.NUMERIC_SCALE = dt.Rows[i]["NUMERIC_SCALE"].ToString();
                    column.ConvertType();
                    //列说明
                    column.Desc = dt.Rows[i]["说明"].ToString();
                    //是否可空
                    column.IsNullAble = dt.Rows[i]["是否可空"].ToString() == "NO" ? false : true;
                    columns.Add(column);
                }
            }
            view.Columns = columns;
            return view;
        }

        /// <summary>
        /// 给表填充详细元数据信息
        /// </summary>
        /// <param name="table">表</param>
        /// <param name="filltablesimpleinfo">是否需要填充表简要信息</param>
        private Table FillTableDetail(SqlServerTable table, bool filltablesimpleinfo)
        {
            using (db.OpenLongConnectOnce())
            {
                if (filltablesimpleinfo) FillTableSimpleInfo(table);
                FillColumns(table);
                FillTriggers(table);
                FillConstraints(table);
                FillIndexes(table);
                ModifyColProperties(table);
                ModifyTableProperties(table);
                return table;
            };
        }

        /// <summary>
        /// 根据表的约束和索引信息,修改表的列信息
        /// </summary>
        /// <param name="table">表</param>
        private void ModifyColProperties(Table table)
        {
            if (table.Columns.IsNullOrEmpty()) return;
            if (table.Constraints.IsNotNullOrEmpty())
            {
                for (var i = 0; i < table.Constraints.Count; i++)
                {
                    var constraint = table.Constraints[i];
                    if (constraint.Type == EnumConstraintType.Unique)
                    {
                        if (!constraint.Constraintkeys.Contains(","))
                        {
                            var column = table.Columns.FirstOrDefault(col => col.Name == constraint.Constraintkeys);
                            column.IsUnique = true;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 根据索引信息修改表属性
        /// </summary>
        /// <param name="table"></param>
        private void ModifyTableProperties(SqlServerTable table)
        {
            SqlServerIndex index = null;
            index = table.Indexes.FirstOrDefault(i => i.IsClustered || i.IndexType == "HEAP") as SqlServerIndex;
            if (index != null)
            {
                table.DataSpaceName = index.DataSpaceName;
                table.DataSpaceType = index.DataSpaceType;
                table.DataSpaceTypeDesc = index.DataSpaceTypeDesc;
            }
        }

        /// <summary>
        /// 根据表名填充表说明/创建时间/更新时间/架构
        /// </summary>
        /// <param name="table"></param>
        private void FillTableSimpleInfo(SqlServerTable table)
        {
            if (table == null) return;
            using (db.OpenLongConnectOnce())
            {
                string sql = $@"
select t.*,o.create_date,o.modify_date, e.value from INFORMATION_SCHEMA.TABLES t
left join sys.objects o on OBJECT_ID(t.TABLE_NAME)=o.object_id 
left join sys.extended_properties e on  OBJECT_ID(t.TABLE_NAME)=e.major_id and e.minor_id=0 and e.name='MS_Description'
 where t.TABLE_TYPE='BASE TABLE' and t.TABLE_NAME='{table.Name}'";
                if (!string.IsNullOrWhiteSpace(table.SchemaName))
                {
                    sql += $" and t.TABLE_SCHEMA='{table.SchemaName}'";
                }
                DataTable dt = db.SelectDataTable(sql);
                if (dt.Rows.Count > 0)
                {
                    table.Name = dt.Rows[0]["TABLE_NAME"].ToString();
                    table.Desc = dt.Rows[0]["value"].ToString();
                    var createTime = dt.Rows[0]["create_date"].ToString();
                    if (!string.IsNullOrWhiteSpace(createTime))
                    {
                        table.CreateTime = DateTime.Parse(createTime);
                    }
                    table.SchemaName = dt.Rows[0]["TABLE_SCHEMA"].ToString();
                    var updatetime = dt.Rows[0]["modify_date"].ToString();
                    if (!string.IsNullOrWhiteSpace(updatetime))
                    {
                        table.LastUpdate = DateTime.Parse(updatetime);
                    }
                    sql = $"exec sp_spaceused '{table.SchemaName}.{table.Name}'";
                    var dt2 = db.SelectDataTable(sql);
                    table.RowCount = int.Parse(dt2.Rows[0]["rows"].ToString());
                    table.ReservedSize = dt2.Rows[0]["reserved"].ToString();
                    table.DataSize = dt2.Rows[0]["data"].ToString();
                    table.IndexSize = dt2.Rows[0]["index_size"].ToString();
                    table.UnUsedSize = dt2.Rows[0]["unused"].ToString();
                }
            };
        }

        /// <summary>
        /// 返回指定表的所有列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<Column> ShowTableColumns(string tableName, string schemaName = null)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return null;
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            var table = new SqlServerTable()
            {
                Name = tableName,
                SchemaName = schemaName
            };
            using (db.OpenLongConnectOnce())
            {
                FillTableSimpleInfo(table);
                FillColumns(table);
            };
            return table.Columns;
        }

        /// <summary>
        /// 根据表名填充列信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillColumns(Table table)
        {
            if (table == null) return;
            string sql = $@"

select 
	序号=ROW_NUMBER() OVER(order BY ORDINAL_POSITION),
	列名=T.COLUMN_NAME,
	类型=T.DATA_TYPE,
    T.CHARACTER_MAXIMUM_LENGTH,
	T.NUMERIC_PRECISION,
	T.NUMERIC_PRECISION_RADIX,
	T.NUMERIC_SCALE,
	T.DATETIME_PRECISION,
	说明=tt.说明,
	是否可空=T.IS_NULLABLE,
    自增种子=IDENT_SEED('{table.SchemaName}.{table.Name}'),
    增量=IDENT_INCR('{table.SchemaName}.{table.Name}'),
	默认值=T.COLUMN_DEFAULT,
	c.是否自增,
    tt.is_computed,
    tt.definition,
    tt.is_persisted    
from INFORMATION_SCHEMA.COLUMNS T
--关联列说明
left outer join
(select 列名=c.name,说明=p.value ,cc.is_computed,cc.definition,cc.is_persisted
from sys.columns c 
    left outer join sys.computed_columns cc on c.object_id=cc.object_id and c.column_id=cc.column_id
	left outer join sys.extended_properties p on c.column_id = p.minor_id and c.object_id=p.major_id
where c.object_id=OBJECT_ID('{table.SchemaName}.{table.Name}'))  tt
on t.COLUMN_NAME=tt.列名
--关联自增信息
left outer join
(SELECT TABLE_NAME,COLUMN_NAME,TABLE_SCHEMA, 是否自增=case (COLUMNPROPERTY(      
      OBJECT_ID('{table.SchemaName}.{table.Name}'),COLUMN_NAME,'IsIdentity')) when 1 then 'Yes' else 'No' end  FROM INFORMATION_SCHEMA.columns) c
on c.COLUMN_NAME =T.COLUMN_NAME and c.TABLE_NAME=t.TABLE_NAME and c.TABLE_SCHEMA=t.TABLE_SCHEMA
where t.TABLE_NAME='{table.Name}' and t.TABLE_SCHEMA='{table.SchemaName}'
and c.TABLE_NAME='{table.Name}'";
            DataTable dt = db.SelectDataTable(sql);
            List<Column> columns = new List<Column>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    var column = new SqlServerColumn();
                    column.Name = dt.Rows[i]["列名"].ToString();
                    column.TableName = table.Name;
                    //列类型
                    column.DATA_TYPE = dt.Rows[i]["类型"].ToString();
                    column.CHARACTER_MAXIMUM_LENGTH = dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    column.NUMERIC_PRECISION = dt.Rows[i]["NUMERIC_PRECISION"].ToString();
                    column.DATETIME_PRECISION = dt.Rows[i]["DATETIME_PRECISION"].ToString();
                    column.NUMERIC_SCALE = dt.Rows[i]["NUMERIC_SCALE"].ToString();
                    column.ConvertType();
                    //列说明
                    column.Desc = dt.Rows[i]["说明"].ToString();
                    //是否可空
                    column.IsNullAble = dt.Rows[i]["是否可空"].ToString() == "NO" ? false : true;
                    //自增
                    column.IsIdentity = dt.Rows[i]["是否自增"].ToString() == "No" ? false : true;
                    if (column.IsIdentity)
                    {
                        column.IdentityStart = int.Parse(dt.Rows[i]["自增种子"].ToString());
                        column.IdentityIncre = int.Parse(dt.Rows[i]["增量"].ToString());
                    }
                    //默认值
                    var de = dt.Rows[i]["默认值"].ToString();
                    column.HasDefault = string.IsNullOrEmpty(de) ? false : true;
                    //计算列
                    column.IsComputed = dt.Rows[i]["is_computed"].ToString() == "True";
                    if (column.IsComputed)
                    {
                        column.ComputedDefinition = dt.Rows[i]["definition"].ToString();
                        column.IsPersisted = dt.Rows[i]["is_persisted"].ToString() == "True";
                    }
                    if (column.HasDefault) column.Default = dt.Rows[i]["默认值"].ToString();
                    columns.Add(column);
                    //唯一:通过唯一约束修正
                }
            }
            table.Columns = columns;
            sql = $@"
 select b.column_name
from information_schema.table_constraints a
inner join information_schema.constraint_column_usage b
on a.constraint_name = b.constraint_name
where a.constraint_type = 'PRIMARY KEY' and a.table_name = '{table.Name}' and a.TABLE_SCHEMA='{table.SchemaName}'
";
            dt = db.SelectDataTable(sql);
            table.PrimaryKeyColumns = new List<Column>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string colname = dt.Rows[i][0].ToString();
                    table.PrimaryKey += "," + colname;
                    var col = table.Columns.First<Column>(j => j.Name == colname);
                    table.PrimaryKeyColumns.Add(col);
                    col.IsPrimaryKey = true;
                    if (dt.Rows.Count == 1)
                    {
                        col.IsUnique = true;
                    }
                }
            }
            table.PrimaryKey = (table.PrimaryKey ?? "").Trim(',');
        }

        /// <summary>
        /// 根据表名填充触发器信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillTriggers(Table table)
        {
            table.Triggers = ShowTableTriggers(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 根据表名填充约束信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillConstraints(Table table)
        {
            table.Constraints = ShowTableConstraints(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 根据表名称填充索引信息
        /// </summary>
        /// <param name="table"></param>
        private void FillIndexes(Table table)
        {
            table.Indexes = ShowTableIndexes(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 返回所有的视图(仅用户创建的视图)
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override List<View> ShowViews(string schemaName = null)
        {
            return _showViews(null, null);
        }

        private List<View> _showViews(string viewName, string schemaName = null)
        {
            List<View> views = new List<View>();
            var sql = @"select infov.*,sysv.create_date,sysv.modify_date 
from INFORMATION_SCHEMA.VIEWS infov 
	left join sys.views sysv on infov.TABLE_NAME=sysv.name and infov.TABLE_SCHEMA=(select name from sys.schemas where schema_id=sysv.schema_id)
where sysv.type='V'";
            if (string.IsNullOrWhiteSpace(viewName) && string.IsNullOrWhiteSpace(schemaName))
            {
                //全部
            }
            else if (!string.IsNullOrWhiteSpace(viewName))
            {
                //指定具体视图
                (viewName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(viewName, schemaName);
                sql += $" and TABLE_SCHEMA='{schemaName}' and TABLE_NAME='{viewName}'";
            }
            else if (string.IsNullOrWhiteSpace(viewName))
            {
                //仅指定的模式
                sql += $" and TABLE_SCHEMA='{schemaName}'";
            }
            var dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var view = new SqlServerView()
                {
                    DataBaseName = dt.Rows[i]["TABLE_CATALOG"].ToString(),
                    Name = dt.Rows[i]["TABLE_NAME"].ToString(),
                    CreateSql = dt.Rows[i]["VIEW_DEFINITION"].ToString(),
                    SchemaName = dt.Rows[i]["TABLE_SCHEMA"].ToString()
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    view.CreateTime = DateTime.Parse(createTime);
                }
                var updatetime = dt.Rows[i]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    view.LastUpdate = DateTime.Parse(updatetime);
                }
                views.Add(view);
            }
            if (views.Count > 0)
            {
                //视图说明
                sql = views.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'view', '{v.Name}',null,null) t")
                       .ToStringSeparated("\r\nunion\r\n");
                dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var viewname = dt.Rows[i]["objname"].ToString();
                    var desc = dt.Rows[i]["value"]?.ToString();
                    var v = views.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                    ((SqlServerView)v).Desc = desc;
                }
            }
            return views;
        }

        /// <summary>
        /// 返回所有的序列
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override List<Sequence> ShowSequences(string schemaName = null)
        {
            if (!db.IsSqlServerVersion2012Compatible())
            {
                //SqlServer 2012推出序列
                return new List<Sequence>();
            }
            return _showSequences(null, schemaName);
        }

        /// <summary>
        /// 返回所有的序列
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <param name="seqName">指定序列名称</param>
        /// <returns></returns>
        private List<Sequence> _showSequences(string seqName, string schemaName = null)
        {
            var res = new List<Sequence>();
            var sql = @"select * from (select 
	SCHEMA_NAME(infos.schema_id) as SEQUENCE_SCHEMA,
	infos.name as SEQUENCE_NAME,
	ISnull(TYPE_NAME(infos.system_type_id),TYPE_NAME(infos.user_type_id)) as DATA_TYPE,
	infos.MINIMUM_VALUE,
	infos.MAXIMUM_VALUE,
	infos.START_VALUE,
	infos.INCREMENT,
	infos.CURRENT_VALUE,
	infos.is_cycling as CYCLE_OPTION,
	infos.IS_CACHED,
	infos.CACHE_SIZE,
infos.create_date,infos.modify_date 
from sys.sequences infos) as infos
where 1=1 ";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and infos.SEQUENCE_SCHEMA='{schemaName.Replace("'", "''")}'";
            }
            if (seqName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and infos.SEQUENCE_NAME='{seqName.Replace("'", "''")}'";
            }
            var dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var seq = new SqlServerSequence()
                {
                    DataBaseName = db.DataBase,
                    SchemaName = dt.Rows[i]["SEQUENCE_SCHEMA"].ToString(),
                    Name = dt.Rows[i]["SEQUENCE_NAME"].ToString(),
                    Type = dt.Rows[i]["DATA_TYPE"].ToString(),
                    IsCached = dt.Rows[i]["IS_CACHED"].To<bool>(),
                    CacheSize = dt.Rows[i]["CACHE_SIZE"].ToWithDefault<int?>(null),
                    Increment = long.Parse(dt.Rows[i]["INCREMENT"].ToString()),
                    CurrentValue = long.Parse(dt.Rows[i]["CURRENT_VALUE"].ToString()),
                    IsCycle = dt.Rows[i]["CYCLE_OPTION"].ToString() == "True",
                    StartValue = long.Parse(dt.Rows[i]["START_VALUE"].ToString()),
                    MaxValue = long.Parse(dt.Rows[i]["MAXIMUM_VALUE"].ToString()),
                    MinValue = long.Parse(dt.Rows[i]["MINIMUM_VALUE"].ToString())
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    seq.CreateTime = DateTime.Parse(createTime);
                }
                var updatetime = dt.Rows[i]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    seq.LastUpdate = DateTime.Parse(updatetime);
                }
                res.Add(seq);
            }
            return res;
        }

        /// <summary>
        /// 返回所有的存储过程(仅用户自定义的)
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override List<Procedure> ShowProcedures(string schemaName = null)
        {
            return _showProcedures(null, schemaName);
        }

        private List<Procedure> _showProcedures(string procName, string schemaName = null)
        {
            List<Procedure> res = new List<Procedure>();
            string sql = @"select infor.ROUTINE_CATALOG, infor.ROUTINE_SCHEMA,infor.ROUTINE_NAME,infor.ROUTINE_DEFINITION,infor.CREATED,infor.LAST_ALTERED
from INFORMATION_SCHEMA.ROUTINES infor where ROUTINE_TYPE='PROCEDURE'";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and ROUTINE_SCHEMA='{schemaName.Replace("'", "''")}'";
            }
            if (procName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and ROUTINE_NAME='{procName.Replace("'", "''")}'";
            }
            DataTable dt = db.SelectDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var proc = new SqlServerProcedure()
                    {
                        DataBaseName = dt.Rows[i]["ROUTINE_CATALOG"].ToString(),
                        SchemaName = dt.Rows[i]["ROUTINE_SCHEMA"].ToString(),
                        Name = dt.Rows[i]["ROUTINE_NAME"].ToString(),
                        CreateSql = dt.Rows[i]["ROUTINE_DEFINITION"].ToString()
                    };
                    var createTime = dt.Rows[i]["CREATED"].ToString();
                    if (!string.IsNullOrWhiteSpace(createTime))
                    {
                        proc.CreateTime = DateTime.Parse(createTime);
                    }
                    var updatetime = dt.Rows[i]["LAST_ALTERED"].ToString();
                    if (!string.IsNullOrWhiteSpace(updatetime))
                    {
                        proc.LastUpdate = DateTime.Parse(updatetime);
                    }
                    res.Add(proc);
                }
            }
            if (res.Count > 0)
            {
                //存储过程说明
                sql = res.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'PROCEDURE', '{v.Name}',null,null) t")
                       .ToStringSeparated("\r\nunion\r\n");
                dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var viewname = dt.Rows[i]["objname"].ToString();
                    var desc = dt.Rows[i]["value"]?.ToString();
                    var v = res.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                    ((SqlServerProcedure)v).Desc = desc;
                }
            }
            return res;
        }


        /// <summary>
        /// 返回指定表的所有约束
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<DBUtil.MetaData.Constraint> ShowTableConstraints(string tableName, string schemaName = null)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            using (db.OpenLongConnectOnce())
            {
                var constraints = new List<DBUtil.MetaData.Constraint>();
                var sql = "";
                //查询默认约束
                sql = $@"
select 
SCHEMA_NAME(sysd.schema_id) as schema_name,
syst.name as table_name,
sysd.name,
sysd.type,sysd.type_desc,
sysc.name as column_name,
sysd.create_date,sysd.modify_date,
sysd.is_system_named,sysd.definition 
from sys.default_constraints sysd 
left join sys.tables syst on syst.object_id=sysd.parent_object_id
left join sys.columns sysc on syst.object_id=sysc.object_id and sysc.column_id=sysd.parent_column_id
where sysd.type='D' 
and sysd.parent_object_id=OBJECT_ID('[{schemaName}].[{tableName}]')
";
                var dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var cons = new SqlServerConstraint()
                    {
                        SchemaName = dt.Rows[i]["schema_name"].ToString(),
                        TableName = dt.Rows[i]["table_name"].ToString(),
                        Name = dt.Rows[i]["name"].ToString(),
                        Type = EnumConstraintType.DefaultValue,
                        Constraintkeys = dt.Rows[i]["column_name"].ToString(),
                        IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                        Definition = dt.Rows[i]["definition"].ToString()
                    };
                    var createTime = dt.Rows[i]["create_date"].ToString();
                    if (!string.IsNullOrWhiteSpace(createTime))
                    {
                        cons.CreateTime = DateTime.Parse(createTime);
                    }
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                    {
                        cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                    }
                    constraints.Add(cons);
                }
                //查询检查约束
                sql = $@"
select 
	SCHEMA_NAME(sysc.schema_id) as schema_name,
	syst.name as table_name, 
	sysc.name,
	sysc.create_date,sysc.modify_date,
	sysc.type,sysc.type_desc,
	sysc.definition,sysc.is_system_named
from sys.check_constraints sysc left join sys.tables syst on sysc.parent_object_id=syst.object_id
where 
	sysc.parent_object_id =OBJECT_ID('{tableName}')";
                dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var cons = new SqlServerConstraint()
                    {
                        SchemaName = dt.Rows[i]["schema_name"].ToString(),
                        TableName = dt.Rows[i]["table_name"].ToString(),
                        Name = dt.Rows[i]["name"].ToString(),
                        Type = EnumConstraintType.Check,
                        IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                        Definition = dt.Rows[i]["definition"].ToString()
                    };

                    var createTime = dt.Rows[i]["create_date"].ToString();
                    if (!string.IsNullOrWhiteSpace(createTime))
                    {
                        cons.CreateTime = DateTime.Parse(createTime);
                    }
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                    {
                        cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                    }
                    constraints.Add(cons);
                }
                //查询主键约束和唯一约束
                sql = $@"
select 
	sysk.object_id,SCHEMA_NAME(sysk.schema_id) as schema_name,sysk.name as name, syst.name as table_name,sysc.name as col_name,
	sysk.type,sysk.type_desc,
	sysk.create_date,sysk.modify_date,
	sysk.is_system_named
from sys.key_constraints sysk 
	left join sys.indexes sysi on sysk.name =sysi.name
	left join sys.index_columns sysic on sysi.object_id=sysic.object_id and sysi.index_id=sysic.index_id
	left join sys.columns sysc on sysic.object_id=sysc.object_id and sysic.column_id=sysc.column_id
	left join sys.tables syst on sysi.object_id=syst.object_id
where sysk.parent_object_id =object_id('{tableName}')
order by sysk.object_id";
                dt = db.SelectDataTable(sql);
                int lastobjid = -1;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var objid = int.Parse(dt.Rows[i]["object_id"].ToString());
                    var colname = dt.Rows[i]["col_name"].ToString();
                    if (lastobjid == objid)
                    {
                        constraints.Last().Constraintkeys += "," + colname;
                        continue;
                    }
                    lastobjid = objid;
                    var cons = new SqlServerConstraint()
                    {
                        SchemaName = dt.Rows[i]["schema_name"].ToString(),
                        TableName = dt.Rows[i]["table_name"].ToString(),
                        Name = dt.Rows[i]["name"].ToString(),
                        Constraintkeys = colname,
                        Type = dt.Rows[i]["type"].ToString() == "PK" ? EnumConstraintType.PrimaryKey : EnumConstraintType.Unique,
                        IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True"
                    };
                    var createTime = dt.Rows[i]["create_date"].ToString();
                    if (!string.IsNullOrWhiteSpace(createTime))
                    {
                        cons.CreateTime = DateTime.Parse(createTime);
                    }
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                    {
                        cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                    }
                    constraints.Add(cons);
                }
                //查询外键约束
                sql = $@"
select 
	SCHEMA_NAME(sysfk.schema_id) as schema_name,
	sysfk.name,
	srctablename=(select name from sys.tables syst where sysfkc.parent_object_id=syst.object_id),
	srccolname=(select sysc.name from sys.columns sysc where sysfkc.parent_column_id=sysc.column_id and sysfkc.parent_object_id=sysc.object_id),
	desttablename=(select name from sys.tables syst where sysfkc.referenced_object_id=syst.object_id),
	destcolname=(select sysc.name from sys.columns sysc where sysfkc.referenced_column_id=sysc.column_id and sysfkc.referenced_object_id=sysc.object_id),
	sysfk.create_date,
	sysfk.modify_date,
	sysfk.is_system_named,
	sysfk.delete_referential_action,
	sysfk.delete_referential_action_desc,
	sysfk.update_referential_action,
	sysfk.update_referential_action_desc
from sys.foreign_keys sysfk 
	left join sys.foreign_key_columns sysfkc on sysfk.object_id=sysfkc.constraint_object_id
	left join sys.tables syst on sysfk.parent_object_id=syst.object_id
	left join sys.tables systr on sysfk.referenced_object_id=systr.object_id
	left join sys.columns sysc on sysc.object_id =sysfk.parent_object_id and sysfk.key_index_id=sysc.column_id
where sysfk.parent_object_id =object_id('[{schemaName}].[{tableName}]')";
                dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var cons = new SqlServerConstraint()
                    {
                        SchemaName = dt.Rows[i]["schema_name"].ToString(),
                        TableName = dt.Rows[i]["srctablename"].ToString(),
                        Name = dt.Rows[i]["name"].ToString(),
                        Constraintkeys = dt.Rows[i]["srccolname"].ToString(),
                        ReferenceTableName = dt.Rows[i]["desttablename"].ToString(),
                        ReferenceColumnName = dt.Rows[i]["destcolname"].ToString(),
                        Type = EnumConstraintType.ForeignKey,
                        IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                        Delete_Action = dt.Rows[i]["delete_referential_action_desc"].ToString(),
                        Update_Action = dt.Rows[i]["update_referential_action_desc"].ToString()
                    };
                    var createTime = dt.Rows[i]["create_date"].ToString();
                    if (!string.IsNullOrWhiteSpace(createTime))
                    {
                        cons.CreateTime = DateTime.Parse(createTime);
                    }
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                    {
                        cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                    }
                    constraints.Add(cons);
                }
                return constraints;
            };
        }

        /// <summary>
        /// 返回指定表所有索引
        /// </summary>
        /// <param name="tableName">所属的表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<Index> ShowTableIndexes(string tableName, string schemaName = null)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            List<Index> indexs = new List<Index>();
            string sql = $@"
select 
OBJECT_SCHEMA_NAME(sysi.object_id) as 'schema',
sysd.name  as 'data_space_name', 
sysd.type as 'data_space_type',
sysd.type_desc as 'data_space_type_desc',
sysi.name as '索引名称', 
syst.name as '表名',
sysc.name as '列名',
sysi.type_desc as '索引类型',
sysi.is_unique as '索引是否要求唯一',
sysc.is_nullable as '索引是否允许为空',
sysi.is_primary_key as '是否是主键约束',
sysi.is_unique_constraint as '是否是唯一约束',
sysk.create_date,
sysk.modify_date
from sys.indexes sysi 
left join sys.key_constraints sysk on sysi.object_id=sysk.parent_object_id and sysi.name=sysk.name
left join sys.index_columns sysic on sysi.object_id=sysic.object_id and sysi.index_id=sysic.index_id
left join sys.tables syst on sysi.object_id=syst.object_id
left join sys.columns sysc on sysi.object_id=sysc.object_id and sysic.column_id=sysc.column_id
left join sys.data_spaces sysd on sysi.data_space_id=sysd.data_space_id

where sysi.object_id=OBJECT_ID('{tableName}')
order by sysi.name
";
            var dt = db.SelectDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                SqlServerIndex lastindex = null;
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    var tablename = dt.Rows[i]["表名"].ToString();
                    var colname = dt.Rows[i]["列名"].ToString();
                    var indexname = dt.Rows[i]["索引名称"].ToString();
                    if (lastindex != null && lastindex.TableName == tablename && lastindex.Name == indexname)
                    {
                        lastindex.ColumnNames += "," + colname;
                        continue;
                    }
                    else if (lastindex != null)
                    {
                        indexs.Add(lastindex);
                    }
                    lastindex = new SqlServerIndex()
                    {
                        DataBaseName = db.DataBase,
                        SchemaName = dt.Rows[i]["schema"].ToString(),
                        Name = dt.Rows[i]["索引名称"].ToString(),
                        DataSpaceName = dt.Rows[i]["data_space_name"].ToString(),
                        DataSpaceType = dt.Rows[i]["data_space_type"].ToString(),
                        DataSpaceTypeDesc = dt.Rows[i]["data_space_type_desc"].ToString(),
                        TableName = dt.Rows[i]["表名"].ToString(),
                        ColumnNames = dt.Rows[i]["列名"].ToString(),
                        IsClustered = dt.Rows[i]["索引类型"].ToString() == "CLUSTERED",
                        IndexType = dt.Rows[i]["索引类型"].ToString(),
                        IsNullAble = dt.Rows[i]["索引是否允许为空"].ToString() == "True",
                        IsUnique = dt.Rows[i]["索引是否要求唯一"].ToString() == "True",
                        IsPrimaryKey = dt.Rows[i]["是否是主键约束"].ToString() == "True",
                        IsUniqueKey = dt.Rows[i]["是否是唯一约束"].ToString() == "True"
                    };
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i]["create_date"].ToString()))
                    {
                        lastindex.CreateTime = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                    }
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                    {
                        lastindex.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                    }
                }
                indexs.Add(lastindex);
            }
            return indexs;
        }

        /// <summary>
        /// 返回所有的函数(仅用户自定义的)
        /// </summary>
        /// <returns></returns>
        public override List<Function> ShowFunctions(string schemaName = null)
        {
            return _showFunctions(null, schemaName);
        }

        private List<Function> _showFunctions(string funcName, string schemaName = null)
        {
            var sql = $@"select syso.name,syso.type,syso.type_desc,syso.create_date,syso.modify_date,infor.ROUTINE_CATALOG,infor.ROUTINE_SCHEMA,infor.ROUTINE_DEFINITION
from sys.objects syso left join INFORMATION_SCHEMA.ROUTINES infor on syso.name=infor.ROUTINE_NAME
where type in ('FN','TF','IF')
";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and ROUTINE_SCHEMA='{schemaName.Replace("'", "''")}'";
            }
            if (funcName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and name='{funcName.Replace("'", "''")}'";
            }
            var dt = db.SelectDataTable(sql);
            var res = new List<Function>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var func = new SqlServerFunction()
                {
                    DataBaseName = dt.Rows[i]["ROUTINE_CATALOG"].ToString(),
                    SchemaName = dt.Rows[i]["ROUTINE_SCHEMA"].ToString(),
                    Name = dt.Rows[i]["name"].ToString(),
                    Type = dt.Rows[i]["type"].ToString(),
                    TypeString = dt.Rows[i]["type_desc"].ToString(),
                    CreateSql = dt.Rows[i]["ROUTINE_DEFINITION"].ToString()
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    func.CreateTime = DateTime.Parse(createTime);
                }
                var modify_date = dt.Rows[i]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(modify_date))
                {
                    func.LastUpdate = DateTime.Parse(modify_date);
                }
                res.Add(func);
            }
            if (res.Count > 0)
            {
                //函数说明
                sql = res.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'FUNCTION', '{v.Name}',null,null) t")
                       .ToStringSeparated("\r\nunion\r\n");
                dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var viewname = dt.Rows[i]["objname"].ToString();
                    var desc = dt.Rows[i]["value"]?.ToString();
                    var v = res.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                    ((SqlServerFunction)v).Desc = desc;
                }
            }
            return res;
        }

        /// <summary>
        /// 返回触发器集合
        /// </summary>
        /// <param name="tableName">所属的表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<Trigger> ShowTableTriggers(string tableName, string schemaName = null)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return new List<Trigger>();
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            List<Trigger> triggers = new List<Trigger>();
            var sql = $@"
select syst.name as tablename, 
	systri.name as trigger_name,
	trigger_owner = user_name(ObjectProperty( systri.object_id, 'ownerid')),
	trigger_schema = schema_name(syso.schema_id),
	systri.create_date,
	systri.modify_date,
	systri.is_disabled,
	systri.is_not_for_replication,
	systri.is_instead_of_trigger,	
	isupdate = ObjectProperty( systri.object_id, 'ExecIsUpdateTrigger'),
	isdelete = ObjectProperty( systri.object_id, 'ExecIsDeleteTrigger'),
	isinsert = ObjectProperty( systri.object_id, 'ExecIsInsertTrigger'),
	isafter = ObjectProperty( systri.object_id, 'ExecIsAfterTrigger'),
	isinsteadof = ObjectProperty( systri.object_id, 'ExecIsInsteadOfTrigger'),
	createsql=OBJECT_DEFINITION(systri.object_id)	
from sys.triggers systri 
left join sys.tables syst on systri.parent_id=syst.object_id
left join sys.objects syso on systri.object_id=syso.object_id
where systri.parent_id=object_id('[{schemaName}].[{tableName}]')
";
            DataTable dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                string name = dt.Rows[i]["trigger_name"].ToString();
                schemaName = dt.Rows[i]["trigger_owner"].ToString();
                bool isupdate = dt.Rows[i]["isupdate"].ToString() == "0" ? false : true;
                bool isdelete = dt.Rows[i]["isdelete"].ToString() == "0" ? false : true;
                bool isinsert = dt.Rows[i]["isinsert"].ToString() == "0" ? false : true;
                bool isafter = dt.Rows[i]["isafter"].ToString() == "0" ? false : true;
                bool isinsteadof = dt.Rows[i]["isInsteadof"].ToString() == "0" ? false : true;
                bool is_disabled = dt.Rows[i]["is_disabled"].ToString() == "False" ? false : true;
                bool is_not_for_replication = dt.Rows[i]["is_not_for_replication"].ToString() == "False" ? false : true;
                string createsql = dt.Rows[i]["createsql"].ToString();
                Trigger tri = new SqlServerTrigger()
                {
                    Name = name,
                    CreateSql = createsql,
                    TableName = tableName,
                    IsInsert = isinsert,
                    IsUpdate = isupdate,
                    IsDelete = isdelete,
                    IsAfter = isafter,
                    IsInsteadof = isinsteadof,
                    IsDisable = is_disabled,
                    IsNotForReplication = is_not_for_replication
                };
                var createTime = dt.Rows[0]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    tri.CreateTime = DateTime.Parse(createTime);
                }
                var updatetime = dt.Rows[0]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    tri.LastUpdate = DateTime.Parse(updatetime);
                }
                triggers.Add(tri);
            }
            return triggers;
        }
        #endregion

        #region 修改元数据
        /// <summary>
        /// 重命名表名
        /// </summary>
        /// <param name="oldName">旧表名</param>
        /// <param name="newName">新表名</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <remarks>注意: 不可能在修改表名时改变所属shcema</remarks>
        /// <returns></returns>
        public override Result RenameTable(string oldName, string newName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                var old_table = db.QuoteIdentifierWithDefaultSchema(oldName, schemaName);
                string sql = $"exec sp_rename '{old_table}','{db.UnQuoteIdentifier(newName)}'";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 重命名列名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="oldName">旧列名</param>
        /// <param name="newName">新列名</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result RenameColumn(string tableName, string oldName, string newName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                tableName = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
                string sql = $"EXEC sp_rename 'tableName.{db.QuoteIdentifierIfNot(oldName)}','{db.UnQuoteIdentifier(newName)}','COLUMN';";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改表说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateTableDescription(string tableName, string desc, string schemaName = null)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            return UpdateExtendedProperty(schemaName, "table", tableName, null, null, "MS_Description", desc);
        }

        /// <summary>
        /// 修改视图说明
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateViewDescription(string viewName, string desc, string schemaName = null)
        {
            (viewName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(viewName, schemaName);
            return UpdateExtendedProperty(schemaName, "view", viewName, null, null, "MS_Description", desc);
        }

        /// <summary>
        /// 修改函数说明
        /// </summary>
        /// <param name="funcName">函数名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public Result UpdateFuncDescription(string funcName, string desc, string schemaName = null)
        {
            (funcName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(funcName, schemaName);
            return UpdateExtendedProperty(schemaName, "FUNCTION", funcName, null, null, "MS_Description", desc);
        }

        /// <summary>
        /// 修改存储过程说明
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public Result UpdateProcDescription(string procName, string desc, string schemaName = null)
        {
            (procName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(procName, schemaName);
            return UpdateExtendedProperty(schemaName, "PROCEDURE", procName, null, null, "MS_Description", desc);
        }

        private Result UpdateExtendedProperty(string schemaName, string level1type, string level1name, string level2type, string level2name, string propName, string propValue)
        {
            return Result.Wrap(() =>
            {
                level2type = (level2type == null ? "null" : "'" + level2type + "'");
                level2name = (level2name == null ? "null" : "'" + level2name + "'");
                string sql = $"SELECT count(1) FROM fn_listextendedproperty ('{propName}', 'schema', '{schemaName}', '{level1type}', '{level1name}',{level2type},{level2name});";
                string sql2 = "";
                if (db.SelectScalar<string>(sql) == "1")
                {
                    sql2 = $"EXEC sp_dropextendedproperty @name=N'{propName}', @level0type=N'SCHEMA',@level0name='{schemaName}', @level1type=N'{level1type}',@level1name=N'{level1name}', @level2type={level2type},@level2name={level2name};\r\n";
                }
                sql2 += $"EXEC sys.sp_addextendedproperty @name = N'{propName}', @value = N'{propValue}' , @level0type = N'SCHEMA',@level0name = '{schemaName}', @level1type = N'{level1type}',@level1name = N'{level1name}', @level2type = {level2type},@level2name = {level2name};";

                db.ExecuteSql(sql2);
            });
        }

        /// <summary>
        /// 删除指定表,如果存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result DropTableIfExist(string tableName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                var name = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
                string sql = $"IF (OBJECT_ID('{name}') is not null) DROP table {name}";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改列说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateColumnDescription(string tableName, string colName, string desc, string schemaName = null)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            return UpdateExtendedProperty(schemaName, "table", tableName, "column", colName, "MS_Description", desc);
        }

        /// <summary>
        /// 修改列说明
        /// </summary>
        /// <param name="viewName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public Result UpdateViewColumnDescription(string viewName, string colName, string desc, string schemaName = null)
        {
            (viewName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(viewName, schemaName);
            return UpdateExtendedProperty(schemaName, "view", viewName, "column", colName, "MS_Description", desc);
        }

        /// <summary>
        /// 删除指定表的指定列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">要删除的列名</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result DropColumn(string tableName, string columnName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                string sql = $"ALTER TABLE {db.QuoteIdentifierWithDefaultSchema(tableName, schemaName)}] DROP COLUMN {db.QuoteIdentifierIfNot(columnName)}";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 改变指定表的指定列类型
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="columnType">列类型</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public Result AlterColumnType(string tableName, string columnName, string columnType, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                tableName = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
                string sql = string.Format("alter table {0} alter column {1} {2}", tableName, db.QuoteIdentifierIfNot(columnName), columnType);
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改指定表的指定列是否可以为空
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="canNull">是否可空</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <param name="columnType">列类型,如果columnType为空,程序会自动探索列类型</param>
        /// <returns></returns>
        public Result AlterColumnNullAble(string tableName, string columnName, bool canNull, string columnType, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                using (db.OpenLongConnectOnce())
                {
                    if (string.IsNullOrWhiteSpace(columnType))
                    {
                        var table = new SqlServerTable() { Name = tableName };
                        FillColumns(table);
                        columnType = table.Columns.FirstOrDefault(i => i.Name == columnName)?.TypeString;
                    }
                    if (string.IsNullOrWhiteSpace(columnType)) throw new Exception($"无法确定表{tableName}列{columnName}的类型,无法修改!");
                    tableName = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
                    string sql = string.Format("alter table {0} alter column {1} {2} {3}", tableName, db.QuoteIdentifierIfNot(columnName), columnType, canNull ? "null" : "not null");
                    db.ExecuteSql(sql);
                };
            });
        }

        /// <summary>
        /// 获取指定表、指定列的默认约束名称
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <param name="schemaName"></param>
        /// <returns></returns>
        private string GetColumnDefaultConstraint(string tableName, string columnName, string schemaName = null)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            // 不指定schema搜索默认约束
            string sql = $@"
select name from sys.objects obj 
where 
obj.object_id=(
	select sysc.default_object_id 
	from sys.columns sysc 
	where 
		sysc.name='{db.UnQuoteIdentifier(columnName)}' 
		and 
		sysc.object_id=
			(select object_id 
				from sys.objects 
				where name='{tableName}'
			)
)";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                // 指定schema搜索默认约束
                sql = $@"
select name from sys.objects obj 
where 
obj.object_id=(
	select sysc.default_object_id 
	from sys.columns sysc 
	where 
		sysc.name='{db.UnQuoteIdentifier(columnName)}' 
		and 
		sysc.object_id=
			(select object_id 
				from sys.objects 
				where name='{tableName}' 
				and schema_id=(select schema_id from sys.schemas where name='{schemaName}')
			)
)";
            }
            return db.SelectScalar<string>(sql);
        }

        /// <summary>
        /// 给指定列修改默认值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="def">默认值</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public Result AlterColumnDefault(string tableName, string columnName, string def, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);

                using (db.OpenLongConnectOnce())
                {
                    string defname = GetColumnDefaultConstraint(tableName, columnName, schemaName);
                    var _tableName = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
                    if (defname.IsNullOrEmptyOrWhiteSpace())
                    {
                        db.ExecuteSql($"ALTER TABLE {_tableName} DROP CONSTRAINT {defname}");
                    }
                    if (def != null)
                    {
                        var sql = $"ALTER TABLE {_tableName} ADD CONSTRAINT DF_gene_{tableName}_{columnName} DEFAULT ('{def}') FOR {db.QuoteIdentifierIfNot(columnName)}";
                        db.ExecuteSql(sql);
                    }
                };
            });
        }

        /// <summary>
        /// 删除指定表指定列的默认值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public Result DropColumnDefault(string tableName, string columnName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                using (db.OpenLongConnectOnce())
                {
                    string defname = GetColumnDefaultConstraint(tableName, columnName, schemaName);
                    if (defname.IsNotNullOrEmptyOrWhiteSpace())
                    {
                        var _tableName = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
                        db.ExecuteSql($"ALTER TABLE {_tableName} DROP CONSTRAINT {defname}");
                    }
                };
            });
        }

        /// <summary>
        /// 给指定表添加一列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="column">列</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result AddColumn(string tableName, Column column, string schemaName = null)
        {
            var _tableName = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
            var pureName = db.AnalysisIdentifier(tableName).identityName;
            return Result.Wrap(() =>
            {
                string sql = " alter table " + _tableName + " add " + db.QuoteIdentifierIfNot(column.Name) + " " + column.TypeString;
                string sqlDesc = "";
                if (!string.IsNullOrWhiteSpace(column.Desc))
                {
                    sqlDesc = $"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{db.DealSqlInject(column.Desc)}' , @level0type=N'SCHEMA',@level0name=OBJECT_SCHEMA_NAME(OBJECT_ID('{_tableName}')), @level1type=N'TABLE',@level1name=N'{pureName}', @level2type=N'COLUMN',@level2name=N'{column.Name}'";
                }
                if (column.IsNullAble)
                {
                    sql += " null";
                }
                else
                {
                    sql += " not null";
                }
                if (column.IsIdentity)
                {
                    sql += " identity(" + column.IdentityStart + "," + column.IdentityIncre + ")";
                }
                if (column.IsUnique)
                {
                    sql += " unique";
                }
                if (column.HasDefault)
                {
                    sql += " default '" + db.DealSqlInject(column.Default) + "'";
                }
                sql += "\r\n" + sqlDesc;
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 设置指定列是否是唯一的
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,可以是多个列,如:"Name,Age",如果指定多个列,那么会先删除单独列的唯一约束</param>
        /// <param name="canUnique">是否是唯一的</param>
        /// <param name="schemaName">指定schema名称</param>
        public Result AlterColumnUnique(string tableName, string columnName, bool canUnique, string schemaName = null)
        {
            var (pureTable, pureSchema) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            var pureCol = db.UnQuoteIdentifier(columnName);
            var quoteTable = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
            return Result.Wrap(() =>
            {
                using (db.OpenLongConnectOnce())
                {
                    var constraints = new List<string>();
                    var arr = columnName.SplitAndTrimTo<string>(",");
                    foreach (var col in arr)
                    {
                        string sql = $@"
SELECT idx.name
  FROM sys.indexes idx
  JOIN sys.index_columns idxCol ON (idx.object_id = idxCol.object_id AND
                                   idx.index_id = idxCol.index_id AND
                                   idx.is_unique_constraint = 1)
  JOIN sys.tables tab ON (idx.object_id = tab.object_id)
  JOIN sys.columns col ON (idx.object_id = col.object_id AND
                          idxCol.column_id = col.column_id)
 WHERE 
idx.type=2
and tab.name = '{pureTable}'
and col.name = '{pureCol}'";
                        if (pureSchema.IsNotNullOrEmptyOrWhiteSpace())
                        {
                            sql += $"\r\nand tab.schema_id=SCHEMA_ID('{pureSchema}')";
                        }
                        var constraintName = db.SelectScalar<string>(sql);
                        if (!constraints.Contains(constraintName) && !string.IsNullOrWhiteSpace(constraintName)) constraints.Add(constraintName);
                    }
                    if (columnName.Contains(','))
                    {
                        //组合列 先删除全部的约束
                        if (constraints.Count > 0)
                        {
                            constraints.ForEach(constraintName => db.ExecuteSql(string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", quoteTable, db.QuoteIdentifierIfNot(constraintName))));
                        }
                        if (canUnique)
                        {
                            var tmp = columnName.Replace(",", "_");
                            db.ExecuteSql($"ALTER TABLE {quoteTable} ADD CONSTRAINT UQ_gene_{pureTable}_{tmp} UNIQUE ({columnName})");
                        }
                    }
                    else
                    {
                        //单列
                        if (!canUnique && constraints.Count > 0)
                        {
                            //原来有,指定的没有 则删除唯一约束
                            constraints.ForEach(constraintName => db.ExecuteSql(string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", quoteTable, db.QuoteIdentifierIfNot(constraintName))));
                        }
                        if (canUnique && constraints.Count == 0)
                        {
                            //原来没有,指定的有 增加唯一约束
                            db.ExecuteSql($"ALTER TABLE {quoteTable} ADD CONSTRAINT UQ_gene_{pureTable}_{columnName} UNIQUE ({columnName})");
                        }
                    }
                };
            });
        }

        /// <summary>
        /// 创建表,注意创建的内容有:表和列说明/列类型/列自增/列的非空/列的唯一/列的默认值/主键
        /// </summary>
        /// <param name="table">表结构</param>
        public override Result CreateTable(Table table)
        {
            if (table == null) return Result.NotOk("必须指定表属性!");
            //最终create语句
            string sql = $@"create table [{table.Name}] (
";
            //主键列名称,如:name,age
            string priname = "";
            //主键名称,如:name_age
            string prikey = "";
            //表说明以及列说明语句
            string sqldesc = "";
            if (!string.IsNullOrWhiteSpace(table.Desc))
            {
                //给表加说明
                sqldesc += $@"
 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{table.Desc}' , @level0type=N'SCHEMA',@level0name=N'{table.SchemaName ?? "dbo"}', @level1type=N'TABLE',@level1name=N'{table.Name}', @level2type=null,@level2name=null";
            }
            table.Columns.ForEach(i =>
            {
                string ideSql = "";
                string nullSql = "";
                string defSql = "";
                string uniSql = "";
                if (i.IsIdentity)
                {
                    ideSql = "identity(" + i.IdentityStart + "," + i.IdentityIncre + ")";
                }
                if (i.IsUnique)
                {
                    uniSql = "unique";
                }
                if (!i.IsNullAble)
                {
                    nullSql = "not null";
                }
                if (!string.IsNullOrWhiteSpace(i.Default))
                {
                    defSql = " default '" + i.Default + "'";
                }
                if (i.IsPrimaryKey)
                {
                    priname += "_" + i.Name;
                    prikey += "," + i.Name;
                }

                sql += string.Format(@" [{0}] {1} {2} {3} {4} {5},
", i.Name, i.TypeString, nullSql, defSql, ideSql, uniSql);
                if (i.Desc != "" && i.Desc != null)
                {
                    sqldesc += $@"
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{i.Desc}' , @level0type=N'SCHEMA',@level0name=N'{table.SchemaName ?? "dbo"}', @level1type=N'TABLE',@level1name=N'{table.Name}', @level2type=N'COLUMN',@level2name=N'{i.Name}'
";
                }
            });
            priname = priname.Trim('_');
            prikey = prikey.Trim(',');
            if (prikey.Contains(","))
            {
                string[] arr = prikey.Split(',');
                prikey = "";
                for (int i = 0; i < arr.Length; i++)
                {
                    prikey += "[" + arr[i] + "],";
                }
                prikey = prikey.Trim(',');
            }
            string sqlPri = $@"
ALTER TABLE [{table.Name}] ADD CONSTRAINT PK_gene_{table.Name}_{priname} PRIMARY KEY({prikey})";
            if (prikey == "")
            {
                sqlPri = "";
            }
            sql += @"
)
";
            return Result.Wrap(() =>
            {
                using (db.OpenLongConnectOnce())
                {

                    db.ExecuteSql(sql);
                    db.ExecuteSql(sqlPri);
                    db.ExecuteSql(sqldesc);
                };
            });
        }

        /// <summary>
        /// 删除存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result DropProcedureIfExist(string procedureName, string schemaName = null)
        {
            (procedureName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(procedureName, schemaName);
            var sql = $"if (exists (select * from sys.procedures where name = '{procedureName}')) drop proc [{procedureName}]";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql = $"if (exists (select * from sys.procedures where name = '{procedureName}' and schema_id=schema_id('{schemaName}'))) drop proc [{schemaName}].[{procedureName}]";
            }
            return Result.Wrap(() => db.ExecuteSql(sql));
        }

        /// <summary>
        /// 删除触发器
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result DropTriggerIfExist(string triggerName, string schemaName = null)
        {
            (triggerName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(triggerName, schemaName);
            var sql = $"IF EXISTS(SELECT * FROM sys.triggers WHERE name = N'{triggerName}') drop trigger [{triggerName}]; ";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql = $"IF EXISTS(SELECT * FROM sys.triggers WHERE name = N'{triggerName}' and SCHEMA_ID('{schemaName}')=(select schema_id from sys.objects syso where syso.object_id= parent_id)) drop trigger {db.QuoteIdentifierWithDefaultSchema(triggerName, schemaName)}; ";
            }
            return Result.Wrap(() => db.ExecuteSql(sql));
        }

        #endregion        

        #region 显示数据库的备份记录 ShowBackUpHistory()
        /// <summary>
        /// 显示数据库的备份记录
        /// </summary>
        /// <returns></returns>
        public List<SqlServerBackUpHistory> ShowBackUpHistory()
        {
            var sql = $@"
SELECT 
     bs.backup_set_id,
     bs.database_name,
	 bs.name as backup_set_name,
	 bs.user_name,
	 bs.expiration_date,
     bs.backup_start_date,
     bs.backup_finish_date,
     CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
     CAST(DATEDIFF(second, bs.backup_start_date,
     bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
     CASE bs.[type]
         WHEN 'D' THEN 'Full Backup'
         WHEN 'I' THEN 'Differential Backup'
         WHEN 'L' THEN 'TLog Backup'
         WHEN 'F' THEN 'File or filegroup'
         WHEN 'G' THEN 'Differential file'
         WHEN 'P' THEN 'Partial'
         WHEN 'Q' THEN 'Differential Partial'
     END AS BackupType,
     bmf.physical_device_name,
     CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
     CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
     bs.server_name,
     bs.recovery_model
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 where database_name='{db.DataBase}'
 ORDER BY bs.backup_start_date;";
            return db.SelectModels<SqlServerBackUpHistory>(sql);
        }
        #endregion

        #region 显示数据库的恢复记录 ShowRestoreHistory()
        /// <summary>
        /// 显示数据库的恢复记录
        /// </summary>
        /// <returns></returns>
        public List<SqlServerRestoreHistory> ShowRestoreHistory()
        {
            var sql = $@"
SELECT 
     rs.[restore_history_id],
     rs.[restore_date],
     rs.[destination_database_name],
     bmf.physical_device_name,
	 bs.name as backup_set_name,
     rs.[user_name],
     rs.[backup_set_id],
     CASE rs.[restore_type]
         WHEN 'D' THEN 'Database'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'V' THEN 'Verifyonly'
     END AS RestoreType,
     rs.[replace],
     rs.[recovery],
     rs.[restart],
     rs.[stop_at],
     rs.[device_count],
     rs.[stop_at_mark_name],
     rs.[stop_before]
FROM [msdb].[dbo].[restorehistory] rs
INNER JOIN [msdb].[dbo].[backupset] bs
--on rs.backup_set_id = bs.media_set_id
ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
ON bs.media_set_id = bmf.media_set_id
where destination_database_name='{db.DataBase}'
order by restore_date;";
            return db.SelectModels<SqlServerRestoreHistory>(sql);
        }
        #endregion

        #region 显示数据库的整体信息 ShowDataBaseInfo()
        /// <summary>
        /// 显示数据库的整体信息
        /// </summary>
        /// <returns></returns>
        public SqlServerDataBaseInfo ShowDataBaseInfo()
        {
            using (db.OpenLongConnectOnce())
            {
                var info = new SqlServerDataBaseInfo();
                //磁盘剩余空间
                var dt = db.SelectDataTable("Exec master.dbo.xp_fixeddrives");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    info.DriverRemainSpaces.Add((dt.Rows[i][0].ToString(), long.Parse(dt.Rows[i][1].ToString())));
                }
                //sp_server_info
                dt = db.SelectDataTable("Exec sp_server_info");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var attr_name = dt.Rows[i]["attribute_name"].ToString();
                    var attr_value = dt.Rows[i]["attribute_value"].ToString();
                    if (attr_name == "DBMS_NAME")
                    {
                        info.DBMS_NAME = attr_value;
                    }
                    else if (attr_name == "DBMS_VER")
                    {
                        info.DBMS_VER = attr_value;
                    }
                    else if (attr_name == "COLLATION_SEQ")
                    {
                        info.Collection_Seq = attr_value;
                    }
                    else if (attr_name == "SYS_SPROC_VERSION")
                    {
                        info.SYS_SPROC_VERSION = attr_value;
                    }
                }
                //数据库版本信息
                info.FullVersion = db.SelectScalar<string>("select @@VERSION");
                //sp_spaceused
                var ds = db.SelectDataSet("exec sp_spaceused");
                info.DBName = ds.Tables[0].Rows[0]["databse_name"].ToString();
                info.DBSize = ds.Tables[0].Rows[0]["database_size"].ToString();
                info.UnAllocatedSize = ds.Tables[0].Rows[0]["unallocated space"].ToString();
                info.Reserved = ds.Tables[1].Rows[0]["reserved"].ToString();
                info.DataSize = ds.Tables[1].Rows[0]["data"].ToString();
                info.IndexSize = ds.Tables[1].Rows[0]["index_size"].ToString();
                info.UnUsedSize = ds.Tables[1].Rows[0]["unused"].ToString();
                //DBCC SQLPERF(LOGSPACE)
                dt = db.SelectDataTable("DBCC SQLPERF(LOGSPACE)");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var dbname = dt.Rows[i]["Database Name"].ToString();
                    var logsize = dt.Rows[i][1].ToString();
                    var logpercent = dt.Rows[i][2].ToString();
                    if (dbname == db.DataBase)
                    {
                        info.LogStat = (double.Parse(logsize), double.Parse(logpercent));
                    }
                }
                //select * from sys.databases
                var createTime = db.SelectScalar<string>($"select create_date from sys.databases where name='{db.DataBase}'");
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    info.CreateTime = DateTime.Parse(createTime);
                }
                return info;
            };
        }
        #endregion

        #region 显示文件组和日志信息 ShowFileGroups()
        /// <summary>
        /// 显示文件组和日志信息
        /// </summary>
        /// <returns></returns>
        public (List<SqlServerFileGroup> FileGroups, List<SqlServerFile> LogFiles) ShowFileGroups()
        {
            List<SqlServerFileGroup> groups = null;
            List<SqlServerFile> files = null;
            using (db.OpenLongConnectOnce())
            {
                groups = db.SelectModels<SqlServerFileGroup>("select * from sys.filegroups");
                files = db.SelectModels<SqlServerFile>("select * from sys.database_files");
            };
            for (int i = files.Count - 1; i >= 0; i--)
            {
                var file = files[i];
                var group = groups.Find(g => g.Data_Space_Id == file.Data_Space_Id);
                if (group != null)
                {
                    group.Files.Add(file);
                    files.RemoveAt(i);
                }
            }
            return (groups, files);
        }
        #endregion

        #region truncate指定表
        /// <summary>
        /// truncate指定表
        /// </summary>
        /// <param name="schemaName">模式名</param>
        /// <param name="tableName">表名</param>
        public override void TruncateTable(string tableName, string schemaName = null)
        {
            tableName = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
            db.ExecuteSql($"truncate table {tableName}");
        }
        #endregion

        #region 获取记录数
        /// <summary>
        /// 获取表的记录数
        /// </summary>
        /// <param name="schemaName">模式名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override long GetCount(string tableName, string schemaName = null)
        {
            tableName = db.QuoteIdentifierWithDefaultSchema(tableName, schemaName);
            return db.SelectScalar<long>($"select count(1) from {tableName}");
        }
        #endregion

        #region 生成insert语句 GenerateInsertSql/GenerateInsertSqlFile
        /// <summary>
        /// 根据表名称和过滤条件生成表数据的insert语句(时间戳列[timestamp]不会参与生成)
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="tableName">表名称</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public override (long count, string sql, long total) GenerateInsertSql(string schemaName, string tableName, int limitCount = 0, string filter = null, params IDataParameter[] paras)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            if (string.IsNullOrWhiteSpace(filter)) filter = "";
            var res = "";
            using (db.OpenLongConnectOnce())
            {
                var table = ShowTableDetail(tableName, schemaName);
                res += $"print '[{schemaName}].[{tableName}]: 开始执行insert语句'\r\n";
                var isIdentity = table.Columns.Find(col => col.IsIdentity) != null;
                if (isIdentity)
                {
                    res += $"set IDENTITY_INSERT [{schemaName}].[{tableName}] ON\r\n";
                }
                var insertSql = "";
                var colNames = table.Columns.Where(col => col.TypeString != "timestamp").Select(col => $"[{col.Name}]").ToList();
                insertSql = $"insert into [{schemaName}].[{tableName}]({string.Join(",", colNames)}) ";
                var count = 0;
                using var readerBlock = db.SelectDataReader($"select * from [{schemaName}].[{tableName}] where 1=1 {filter}", paras);
                var reader = readerBlock.Reader;
                while (reader.Read())
                {
                    var sql = insertSql + " values(";
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        if (col.TypeString == "timestamp") continue;
                        var value = reader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + value.ToString().Replace("'", "''") + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(value.ToString().Replace("'", "''"));
                        }
                    }
                    sql += $"{string.Join(",", values)});";
                    res += $"\r\n{sql}";
                    count++;
                    if (limitCount > 0 && limitCount == count) break;
                };
                if (isIdentity)
                {
                    res += $"\r\n\r\nset IDENTITY_INSERT [{schemaName}].[{tableName}] OFF\r\n";
                }
                reader.Close();
                res += $"\r\nprint '[{schemaName}].[{tableName}]: 共执行了{count}次insert(自行判断执行成功或失败)'";
                var total = GetCount(tableName, schemaName);
                return (count, res, total);
            };
        }

        /// <summary>
        /// 根据视图名称和过滤条件生成视图数据的insert语句(时间戳列[timestamp]不会参与生成)
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="viewName">视图名称</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public (long count, string sql, long total) GenerateInsertViewSql(string schemaName, string viewName, int limitCount = 0, string filter = null, params IDataParameter[] paras)
        {
            (viewName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(viewName, schemaName);
            if (string.IsNullOrWhiteSpace(filter)) filter = "";
            var res = "";
            using (db.OpenLongConnectOnce())
            {
                var table = ShowViewDetail(viewName, schemaName);
                res += $"print '[{schemaName}].[{viewName}]: 开始执行insert语句'\r\n";
                var insertSql = "";
                var colNames = table.Columns.Where(col => col.TypeString != "timestamp").Select(col => $"[{col.Name}]").ToList();
                insertSql = $"insert into [{schemaName}].[{viewName}]({string.Join(",", colNames)}) ";
                var count = 0;
                using var readerBlock = db.SelectDataReader($"select * from [{schemaName}].[{viewName}] where 1=1 {filter}", paras);
                var reader = readerBlock.Reader;
                while (reader.Read())
                {
                    var sql = insertSql + " values(";
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        if (col.TypeString == "timestamp") continue;
                        var value = reader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + value.ToString().Replace("'", "''") + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(value.ToString().Replace("'", "''"));
                        }
                    }
                    sql += $"{string.Join(",", values)});";
                    res += $"\r\n{sql}";
                    count++;
                    if (limitCount > 0 && limitCount == count) break;
                };
                reader.Close();
                res += $"\r\nprint '[{schemaName}].[{viewName}]: 共执行了{count}次insert(自行判断执行成功或失败)'";
                var total = GetCount(viewName, schemaName);
                return (count, res, total);
            };
        }

        /// <summary>
        /// 将表数据全部导出到文件
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="tableName">表名</param>
        /// <param name="fileAbsPath">文件的绝对路径</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="paras">参数</param>
        /// <returns>导出的记录数</returns>
        public override long GenerateInsertSqlFile(string schemaName, string tableName, string fileAbsPath, int limitCount = 0, string filter = null, params IDataParameter[] paras)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            using (db.OpenLongConnectOnce())
            {
                var table = ShowTableDetail(tableName, schemaName);
                File.AppendAllText(fileAbsPath, $"print '[{schemaName}].[{tableName}]: 开始执行insert语句'");
                var isIdentity = table.Columns.Find(col => col.IsIdentity) != null;
                if (isIdentity)
                {
                    File.AppendAllText(fileAbsPath, $"set IDENTITY_INSERT [{schemaName}].[{tableName}] ON\r\n");
                }
                var insertSql = "";
                var colNames = table.Columns.Where(col => col.TypeString != "timestamp").Select(col => $"[{col.Name}]").ToList();
                insertSql = $"insert into [{schemaName}].[{tableName}]({string.Join(",", colNames)}) ";
                var count = 0L;
                var countLimit = 0;
                using var readerBlock = db.SelectDataReader($"select * from [{schemaName}].[{tableName}] where 1=1 {filter}", paras);
                var reader = readerBlock.Reader;
                while (reader.Read())
                {
                    var sql = insertSql + " values(";
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        if (col.TypeString == "timestamp") continue;
                        var value = reader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + value.ToString().Replace("'", "''") + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(value.ToString().Replace("'", "''"));
                        }
                    }
                    sql += $"{string.Join(",", values)});";
                    File.AppendAllText(fileAbsPath, $"\r\n{sql}");
                    count++;
                    countLimit++;
                    if (limitCount > 0 && limitCount == countLimit) break;
                };
                if (isIdentity)
                {
                    File.AppendAllText(fileAbsPath, $"\r\n\r\nset IDENTITY_INSERT [{schemaName}].[{tableName}] OFF\r\n");
                }
                reader.Close();
                File.AppendAllText(fileAbsPath, $"\r\nprint '[{schemaName}].[{tableName}]: 共执行了{count}次insert(自行判断执行成功或失败)'");
                return count;
            };
        }

        /// <summary>
        /// 将表视图数据全部导出到文件
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="viewName">视图名</param>
        /// <param name="fileAbsPath">文件的绝对路径</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="paras">参数</param>
        /// <returns>导出的记录数</returns>
        public long GenerateInsertViewSqlFile(string schemaName, string viewName, string fileAbsPath, int limitCount = 0, string filter = null, params IDataParameter[] paras)
        {
            (viewName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(viewName, schemaName);
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            using (db.OpenLongConnectOnce())
            {
                var table = ShowTableDetail(viewName, schemaName);
                File.AppendAllText(fileAbsPath, $"print '[{schemaName}].[{viewName}]: 开始执行insert语句'");
                var insertSql = "";
                var colNames = table.Columns.Where(col => col.TypeString != "timestamp").Select(col => $"[{col.Name}]").ToList();
                insertSql = $"insert into [{schemaName}].[{viewName}]({string.Join(",", colNames)}) ";
                var count = 0L;
                var countLimit = 0;
                using var readerBlock = db.SelectDataReader($"select * from [{schemaName}].[{viewName}] where 1=1 {filter}", paras);
                var reader = readerBlock.Reader;
                while (reader.Read())
                {
                    var sql = insertSql + " values(";
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        if (col.TypeString == "timestamp") continue;
                        var value = reader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + value.ToString().Replace("'", "''") + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(value.ToString().Replace("'", "''"));
                        }
                    }
                    sql += $"{string.Join(",", values)});";
                    File.AppendAllText(fileAbsPath, $"\r\n{sql}");
                    count++;
                    countLimit++;
                    if (limitCount > 0 && limitCount == countLimit) break;
                };
                reader.Close();
                File.AppendAllText(fileAbsPath, $"\r\nprint '[{schemaName}].[{viewName}]: 共执行了{count}次insert(自行判断执行成功或失败)'");
                return count;
            };
        }
        #endregion

        #region 生成建表脚本

        private string GenerateCreateTableSql(Table detail, bool includeForeignKey = true)
        {
            var schemaName = detail.SchemaName;
            var tableName = detail.Name;
            //生成create table
            var sql = $@"IF (OBJECT_ID('[{schemaName}].[{tableName}]') is not null)
begin
    drop table [{schemaName}].[{tableName}];
    print '[{schemaName}].[{tableName}]: 已删除旧表';
end
GO
create table [{schemaName}].[{tableName}](";
            var isUnionKey = detail.PrimaryKeyColumns.Count > 1;
            foreach (var item in detail.Columns)
            {
                var col = item as SqlServerColumn;
                sql += $"\r\n    [{col.Name}] {col.TypeString}";
                if (col.IsIdentity)
                {
                    sql += $" identity({col.IdentityStart},{col.IdentityIncre})";
                }
                if (col.IsPrimaryKey & !isUnionKey)
                {
                    //单列主键
                    sql += $" primary key,";
                    continue;
                }
                if (col.IsComputed)
                {
                    sql += $" as {col.ComputedDefinition}";
                    if (col.IsPersisted.Value) sql += " PERSISTED";
                }
                else
                {
                    if (!col.IsNullAble)
                    {
                        sql += $" not null";
                    }
                    if (col.IsUnique)
                    {
                        sql += " unique";
                    }
                    if (col.HasDefault)
                    {
                        sql += $" default({col.Default})";
                    }
                }
                sql += ",";
            }
            sql = sql.TrimEnd(',');
            sql += "\r\n);";
            sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已创建新表';";
            sql += "\r\nGO";
            //生成表注释
            if (!string.IsNullOrWhiteSpace(detail.Desc))
            {
                sql += $"\r\nEXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc.Replace("'", "''")}' , @level0type = N'SCHEMA',@level0name = '{schemaName}', @level1type = N'TABLE',@level1name = N'{tableName}', @level2type = null,@level2name = null;";
                sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已添加表说明';";
            }
            //生成字段注释
            var b = false;
            foreach (var col in detail.Columns)
            {
                if (!string.IsNullOrWhiteSpace(col.Desc))
                {
                    sql += $"\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{col.Desc.Replace("'", "''")}' , @level0type=N'SCHEMA',@level0name='{schemaName}', @level1type=N'TABLE',@level1name=N'{tableName}', @level2type=N'COLUMN',@level2name=N'{col.Name}'";
                    b = true;
                }
            }
            if (b)
            {
                sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已添加列说明';";
            }
            //生成约束
            b = false;
            foreach (var item in detail.Constraints)
            {
                var constraint = item as SqlServerConstraint;
                if (constraint.Type == EnumConstraintType.Check)
                {
                    //检查约束
                    sql += $"\r\nALTER TABLE [{schemaName}].[{tableName}] ADD CONSTRAINT {constraint.Name} CHECK({constraint.Definition});";
                    b = true;
                }
                else if (constraint.Type == EnumConstraintType.DefaultValue)
                {
                    //默认约束定义在列上
                }
                else if (constraint.Type == EnumConstraintType.ForeignKey)
                {
                    if (includeForeignKey)
                    {
                        //外键约束
                        var detail2 = ShowTableDetail(constraint.TableName, constraint.SchemaName);
                        sql += $"\r\nALTER TABLE [{schemaName}].[{tableName}] ADD CONSTRAINT {constraint.Name} FOREIGN KEY({constraint.Constraintkeys}) REFERENCES [{schemaName}].[{constraint.ReferenceTableName}]({constraint.ReferenceColumnName}) ON DELETE {constraint.Delete_Action.Replace("_", " ")} on UPDATE {constraint.Update_Action.Replace("_", " ")};";
                        b = true;
                    }
                }
                else if (constraint.Type == EnumConstraintType.PrimaryKey)
                {
                    //主键约束, 联合主键写在这
                    if (detail.PrimaryKeyColumns.Count > 1)
                    {
                        sql += $"\r\nALTER TABLE [{schemaName}].[{tableName}] ADD CONSTRAINT {constraint.Name} primary key ({constraint.Constraintkeys});";
                        b = true;
                    }
                }
                else if (constraint.Type == EnumConstraintType.Unique)
                {
                    //唯一约束,联合唯一写在这
                    if (constraint.Constraintkeys.Contains(","))
                    {
                        sql += $"\r\nALTER TABLE [{schemaName}].[{tableName}] ADD CONSTRAINT {constraint.Name} UNIQUE ({constraint.Constraintkeys});";
                        b = true;
                    }
                }
            }
            if (b)
            {
                sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已完成约束';";
            }

            //生成索引
            b = false;
            foreach (var item in detail.Indexes)
            {
                var index = item as SqlServerIndex;
                //Heap类型索引不用单独建立
                if (index.IndexType == "HEAP") continue;
                if (index.IsUniqueKey || index.IsPrimaryKey)
                {
                    //唯一或主键自动生成的,这里不用额外加索引
                    continue;
                }
                var clust = index.IsClustered ? "CLUSTERED" : "NONCLUSTERED";
                var unique = index.IsUnique ? "UNIQUE" : "";
                sql += $"\r\ncreate {unique} {clust} INDEX {index.Name} ON [{schemaName}].[{tableName}]({index.ColumnNames});";
                b = true;
            }
            if (b)
            {
                sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已完成索引';";
            }

            //生成触发器
            b = false;
            foreach (var item in detail.Triggers)
            {
                var trigger = item as SqlServerTrigger;
                sql += $"\r\nGO\r\n{trigger.CreateSql}";
                b = true;
            }
            if (b)
            {
                sql += $"\r\nGO\r\nprint '[{schemaName}].[{tableName}]: 已完成触发器';";
            }

            return sql;
        }
        /// <summary>
        /// 生成建表脚本
        /// </summary>
        /// <param name="schemaName">模式名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override string GenerateCreateTableSql(string tableName, string schemaName = null)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            var detail = ShowTableDetail(tableName, schemaName);
            var builder = new StringBuilder();
            //先检查schema
            if (detail.SchemaName != "dbo")
            {
                //含有dbo意外的schema
                builder.Append($"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                builder.Append($@"if((select 1 from sys.schemas where name='{detail.SchemaName}') is null)
begin
    exec ('create schema {detail.SchemaName}');
    print '新建schema: {detail.SchemaName}';
end
GO");
                builder.Append($"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            var tableSql = GenerateCreateTableSql(detail);
            builder.Append(tableSql);
            return builder.ToString();
        }

        /// <summary>
        /// 生成建视图语句
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="viewName"></param>
        /// <returns></returns>
        public string GenerateCreateViewSql(string viewName, string schemaName = null)
        {
            (viewName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(viewName, schemaName);
            var detail = ShowViewDetail(viewName, schemaName) as SqlServerView;
            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine($@"
IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop view [{detail.SchemaName}].{detail.Name};
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧视图';
end
GO
");
            sqlbuilder.AppendLine(detail.CreateSql.Trim());
            sqlbuilder.AppendLine($@"
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新视图';
GO");
            if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
            {
                sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'view',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加视图说明';");
            }
            var b = false;
            detail.Columns.ForEach(column =>
            {
                if (column.Desc.IsNotNullOrEmptyOrWhiteSpace())
                {
                    sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{column.Desc}' , @level0type=N'SCHEMA',@level0name='{detail.SchemaName}', @level1type=N'view',@level1name=N'{detail.Name}', @level2type=N'COLUMN',@level2name=N'{column.Name}'");
                    b = true;
                }
            });
            if (b)
            {
                sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加列说明';");
            }
            return sqlbuilder.ToString();

        }

        /// <summary>
        /// 生成建序列语句
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="seqName"></param>
        /// <returns></returns>
        public string GenerateCreateSeqSql(string seqName, string schemaName = null)
        {
            (seqName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(seqName, schemaName);
            var detail = _showSequences(seqName, schemaName).FirstOrDefault() as SqlServerSequence;
            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine($@"
IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop SEQUENCE [{detail.SchemaName}].{detail.Name};
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧序列';
end
GO
");
            sqlbuilder.AppendLine(detail.CreateSql.Trim());
            sqlbuilder.AppendLine($@"
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新序列';
GO");
            return sqlbuilder.ToString();

        }

        /// <summary>
        /// 生成建存储过程语句
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="procName"></param>
        /// <returns></returns>
        public string GenerateCreateProcSql(string procName, string schemaName = null)
        {
            (procName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(procName, schemaName);
            var detail = _showProcedures(procName, schemaName).FirstOrDefault() as SqlServerProcedure;
            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine($@"
IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop proc [{detail.SchemaName}].{detail.Name};
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧存储过程';
end
GO");
            sqlbuilder.AppendLine(detail.CreateSql.Trim());
            sqlbuilder.AppendLine($@"GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新存储过程';
GO");
            if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
            {
                sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'PROCEDURE',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加存储过程说明';");
            }
            return sqlbuilder.ToString();
        }

        /// <summary>
        /// 生成建函数语句
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="funcName"></param>
        /// <returns></returns>
        public string GenerateCreateFuncSql(string funcName, string schemaName = null)
        {
            (funcName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(funcName, schemaName);
            var detail = _showFunctions(funcName, schemaName).FirstOrDefault() as SqlServerFunction;
            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine($@"
IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop function [{detail.SchemaName}].{detail.Name};
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧函数';
end
GO");
            sqlbuilder.AppendLine(detail.CreateSql.Trim());
            sqlbuilder.AppendLine($@"GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新函数';
GO");
            if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
            {
                sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'FUNCTION',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加函数说明';");
            }
            return sqlbuilder.ToString();
        }

        /// <summary>
        /// 生成建表脚本文件
        /// </summary>
        /// <param name="tables">表名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <param name="includeInsertSql">是否包含insert脚本</param>
        /// <returns></returns>
        public override void GenerateCreateTableSqlFile(List<Table> tables, string fileAbsPath, bool includeInsertSql = false)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = tables.Select(t => ShowTableDetail(t.Name, t.SchemaName)).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo以外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //如果有外键,先删除外键
            if (details.Any(db => db.Constraints.Any(c => c.Type == EnumConstraintType.ForeignKey)))
            {
                File.AppendAllText(fileAbsPath, $"--------------------------发现外键约束,先删除" + "\r\n");
                File.AppendAllText(fileAbsPath, $"print '发现外键约束,先删除';" + "\r\n");
                //先删除外键约束
                var count = 0;
                details.ForEach(i =>
                {
                    var cons = i.Constraints.Where(i => i.Type == EnumConstraintType.ForeignKey).ToList();
                    cons.ForEach(con =>
                    {
                        var dropCons = $@"IF (OBJECT_ID('{con.SchemaName}.{con.Name}') is not null)
begin
    alter table [{i.SchemaName}].[{i.Name}] drop CONSTRAINT {con.Name};
    print '[{i.SchemaName}].[{i.Name}]: 已删除约束:{con.Name}';
end
GO";
                        File.AppendAllText(fileAbsPath, dropCons + "\r\n");
                        count++;
                    });
                });
                File.AppendAllText(fileAbsPath, $"--------------------------已删除外键约束,开始建表" + "\r\n");
                File.AppendAllText(fileAbsPath, $"print '已删除{count}个外键约束,开始建表...';" + "\r\n\r\n");
            }
            //生成脚本,不包含外键约束
            foreach (var detail in details)
            {
                File.AppendAllText(fileAbsPath, $"--------------------------表:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = GenerateCreateTableSql(detail, false);
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建表结束,共建表:{details.Count}个---------------------" + "\r\n\r\n");
            //插入数据
            if (includeInsertSql)
            {
                File.AppendAllText(fileAbsPath, $"\r\n--------------------------开始插入数据" + "\r\n");
                details.ForEach(detail =>
                {
                    File.AppendAllText(fileAbsPath, $"--------------------------插入到表:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                    var (count, sqlInsert, total) = GenerateInsertSql(detail.SchemaName, detail.Name);
                    File.AppendAllText(fileAbsPath, sqlInsert + "\r\n");
                    File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------------------\r\n");
                });
            }
            //最后创建外键约束
            if (details.Any(db => db.Constraints.Any(c => c.Type == EnumConstraintType.ForeignKey)))
            {
                File.AppendAllText(fileAbsPath, $"---------------------------最后,追加外键约束" + "\r\n");
                File.AppendAllText(fileAbsPath, $"print '最后,追加外键约束';" + "\r\n");
                //外键约束
                var count = 0;
                details.ForEach(detail =>
                {
                    var cons = detail.Constraints.Where(i => i.Type == EnumConstraintType.ForeignKey).ToList();
                    cons.ForEach(constraint =>
                    {
                        var detail2 = ShowTableDetail(constraint.TableName, constraint.SchemaName);
                        var sql = $"\r\nALTER TABLE [{detail.SchemaName}].[{detail.Name}] ADD CONSTRAINT {constraint.Name} FOREIGN KEY({constraint.Constraintkeys}) REFERENCES [{detail.SchemaName}].[{constraint.ReferenceTableName}]({constraint.ReferenceColumnName}) ON DELETE {constraint.Delete_Action.Replace("_", " ")} on UPDATE {constraint.Update_Action.Replace("_", " ")};";
                        File.AppendAllText(fileAbsPath, sql + "\r\n");
                        count++;
                    });
                });
                File.AppendAllText(fileAbsPath, $"---------------------------已完成外键约束追加" + "\r\n");
                File.AppendAllText(fileAbsPath, $"print '已完成外键约束追加,共追加了{count}条外键约束';" + "\r\n\r\n");
            }

            return;
        }

        /// <summary>
        /// 生成建视图脚本文件
        /// </summary>
        /// <param name="views">视图名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <returns></returns>
        public void GenerateCreateViewSqlFile(List<View> views, string fileAbsPath)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = views.Select(t => ShowViewDetail(t.Name, t.SchemaName) as SqlServerView).OrderBy(i => i.CreateTime).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo意外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //生成脚本
            foreach (var detail in details)
            {
                var sqlbuilder = new StringBuilder();
                if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
                {
                    sqlbuilder.AppendLine("GO");
                    sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'view',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                    sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加视图说明';");
                }
                File.AppendAllText(fileAbsPath, $"--------------------------视图:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = $@"IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop view [{detail.SchemaName}].[{detail.Name}];
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧视图';
end
GO
{detail.CreateSql.Trim()}
{sqlbuilder.ToString().Trim()}
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新视图';
GO";
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建视图结束,共建视图:{details.Count}个---------------------" + "\r\n\r\n");
        }

        /// <summary>
        /// 生成建函数脚本文件
        /// </summary>
        /// <param name="funcs">函数名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <returns></returns>
        public void GenerateCreateFuncSqlFile(List<Function> funcs, string fileAbsPath)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = funcs.Select(t => _showFunctions(t.Name, t.SchemaName).FirstOrDefault() as SqlServerFunction).OrderBy(i => i.CreateTime).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo意外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //生成脚本
            foreach (var detail in details)
            {
                var sqlbuilder = new StringBuilder();
                if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
                {
                    sqlbuilder.AppendLine("GO");
                    sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'FUNCTION',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                    sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加函数说明';");
                }
                File.AppendAllText(fileAbsPath, $"--------------------------函数:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = $@"IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop function [{detail.SchemaName}].[{detail.Name}];
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧函数';
end
GO
{detail.CreateSql.Trim()}
{sqlbuilder.ToString().Trim()}
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新函数';
GO";
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建函数结束,共建函数:{details.Count}个---------------------" + "\r\n\r\n");
        }

        /// <summary>
        /// 生成建存储过程脚本文件
        /// </summary>
        /// <param name="procs">存储过程名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <returns></returns>
        public void GenerateCreateProcSqlFile(List<Procedure> procs, string fileAbsPath)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = procs.Select(t => _showProcedures(t.Name, t.SchemaName).FirstOrDefault() as SqlServerProcedure).OrderBy(i => i.CreateTime).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo意外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //生成脚本
            foreach (var detail in details)
            {
                var sqlbuilder = new StringBuilder();
                if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
                {
                    sqlbuilder.AppendLine("GO");
                    sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'PROCEDURE',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                    sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加存储过程说明';");
                }
                File.AppendAllText(fileAbsPath, $"--------------------------存储过程:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = $@"IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop PROCEDURE [{detail.SchemaName}].[{detail.Name}];
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧存储过程';
end
GO
{detail.CreateSql.Trim()}
{sqlbuilder.ToString().Trim()}
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新存储过程';
GO";
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建存储过程结束,共建存储过程:{details.Count}个---------------------" + "\r\n\r\n");
        }

        /// <summary>
        /// 生成建序列脚本文件
        /// </summary>
        /// <param name="seqs">视图名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <returns></returns>
        public void GenerateCreateSeqSqlFile(List<Sequence> seqs, string fileAbsPath)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = seqs.Select(t => _showSequences(t.Name, t.SchemaName).FirstOrDefault() as SqlServerSequence).OrderBy(i => i.CreateTime).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo意外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //生成脚本
            foreach (var detail in details)
            {
                File.AppendAllText(fileAbsPath, $"--------------------------序列:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = $@"IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop SEQUENCE [{detail.SchemaName}].[{detail.Name}];
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧序列';
end
GO
{detail.CreateSql.Trim()}
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新序列';
GO";
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建序列结束,共建序列:{details.Count}个---------------------" + "\r\n\r\n");
        }
        #endregion

        #region 生成增删改查代码
        /// <summary>
        /// 根据表生成增删改查代码
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="tableName">表名称</param>
        /// <returns></returns>
        public override string GenerateCode(string tableName, string schemaName = null)
        {
            return _generateCode(schemaName, tableName);
        }

        /// <summary>
        /// 根据视图的实体代码
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="viewName">视图名称</param>
        /// <returns></returns>
        public string GenerateViewCode(string viewName, string schemaName = null)
        {
            return _generateCode(schemaName, viewName, true);
        }

        private string _generateCode(string tableName, string schemaName, bool isView = false)
        {
            (tableName, schemaName) = db.AnalysisIdentifierWithDefaultSchema(tableName, schemaName);
            var code = "";
            SqlServerTable table = null;
            SqlServerView view = null;
            if (isView)
            {
                view = ShowViewDetail(tableName, schemaName) as SqlServerView;
            }
            else
            {
                table = ShowTableDetail(tableName, schemaName) as SqlServerTable;
            }
            var name = (isView ? view.Name : table.Name);
            code += $@"
using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;
using DotNetCommon.Serialize; // dotnet add package DotNetCommon --version 1.0.0
public class {name}
{{
        public const string TABLE_NAME = ""{name}"";
        public const string KEY_NAME = ""{table?.PrimaryKey}"";
        private static List<string> allColumns;
        static {name}()
        {{
            allColumns = new List<string>();
            var type = typeof({name});
            var props = type.GetProperties().ToList();
            props.ForEach(prop => allColumns.Add(prop.Name));
        }}
        public static List<string> AllColumns => allColumns;
";
            var columns = isView ? view.Columns : table.Columns;
            foreach (var item in columns)
            {
                var col = item as SqlServerColumn;
                var type = "";
                switch (col.Type)
                {
                    case EnumColumnType.Bit:
                        type = "bool";
                        break;
                    case EnumColumnType.Int:
                        type = "int";
                        break;
                    case EnumColumnType.Long:
                        type = "long";
                        break;
                    case EnumColumnType.Float:
                        type = "double";
                        break;
                    case EnumColumnType.Double:
                        type = "double";
                        break;
                    case EnumColumnType.Decimal:
                        type = "decimal";
                        break;
                    case EnumColumnType.String:
                    case EnumColumnType.Text:
                        type = "string";
                        break;
                    case EnumColumnType.Blob:
                        type = "byte[]";
                        break;
                    case EnumColumnType.Bool:
                        type = "bool";
                        break;
                    case EnumColumnType.Date:
                    case EnumColumnType.DateTime:
                        type = "DateTime";
                        break;
                    case EnumColumnType.Other:
                        type = "object";
                        break;
                    default:
                        break;
                }
                if (col.IsNullAble)
                {
                    if (new string[] { "object", "byte[]", "string" }.Contains(type)) { }
                    else
                    {
                        type += "?";
                    }
                }
                var desc = col.Desc;
                desc = desc.Replace("\r\n", " ");
                if (type == "long")
                {
                    code += @$"

        /// <summary>
        /// {desc}
        /// </summary>
        [JsonConverter(typeof(NumberConverter), NumberConverterShip.Int64)]
        public {type} {col.Name} {{ set; get; }}";
                }
                else
                {
                    code += @$"

        /// <summary>
        /// {desc}
        /// </summary>
        public {type} {col.Name} {{ get; set; }}";
                }

            }
            code += "\r\n}";
            return code;
        }
        #endregion
    }
}
